package dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import domain.Document;
import domain.Post;
import domain.Vocabulary;

public class PostDAO extends GenericDAO<Post> {
	public static final String TABLE_NAME = "lista_posteo";
	private Post post;

	public PostDAO() throws ClassNotFoundException, IOException {
		super();
		table = TABLE_NAME;
	}

	@Override
	/*
	 * Inserts a post in the database
	 * @param value, the post to insert
	 */
	protected void onInsert(Post value) throws SQLException {
		query = new StringBuilder("");
		post = value;
		query.append("insert into ");
		query.append(table);
		query.append(" values (?,?,?)");
		Connection conn = getConnection(); 
		statement = conn.prepareStatement(query.toString());
		statement.setInt(1, post.getDocument().getId());
		statement.setInt(2, post.getWordFrequency());
		statement.setInt(3, post.getId());
		statement.executeUpdate();
		
		closeConnection(conn);
	}

	public void insertForVocabulary(Post value, Vocabulary vocabulary) {
		try {
			query = new StringBuilder("");
			post = value;
			query.append("insert into ");
			query.append(table);
			query.append(" values (?,?,?,?)");
			Connection conn = getConnection(); 
			statement = conn.prepareStatement(query.toString());
			statement.setInt(1, 0);
			statement.setInt(2, post.getDocument().getId());
			statement.setInt(3, vocabulary.getId());
			statement.setInt(4, post.getWordFrequency());
			statement.executeUpdate();
			
			closeConnection(conn);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	@Override
	/*
	 * Selects a post from the database, which match with the vocabulary id given
	 * @param id, the id of the post to get
	 * @return p, the post with the id given by parameter
	 */
	protected Post onSelect(Integer id) throws SQLException {
		Post p = new Post();
		query = new StringBuilder("");
		query.append("select id_posteo, id_documento, frecuencia_termino from ");
		query.append(table);
		query.append(" where id_posteo = ?");
		Connection conn = getConnection(); 
		statement = conn.prepareStatement(query.toString());
		statement.setInt(1, id);
		rs = statement.executeQuery();
		while (rs.next()) {
			p.setId(rs.getInt(1));
			p.getDocument().setId(rs.getInt(2));
			p.setWordFrequency(rs.getInt(3));
		}
		
		closeConnection(conn);
		return p;
	}

	public List<Post> select(Vocabulary vocabulary, boolean fetch) throws SQLException {
		List<Post> collection = new ArrayList<Post>();
		Post post = null;
		Document doc = null;
		query = new StringBuilder("");
		query.append("SELECT DISTINCT p.id_posteo, p.frecuencia_termino ");
		if (fetch) {
			query.append(", d.id_documento, d.nombre, d.path, d.size_documento, d.cantidad_total_documentos, d.size_archivo ");
		}
		query.append(" from " + table + " p, ");
		query.append(VocabularyDAO.TABLE_NAME + " v, ");
		query.append(DocumentDAO.TABLE_NAME + " d");
		query.append(" WHERE ");
		query.append("d.id_documento = p.id_documento");
		query.append(" AND ");
		query.append("p.id_vocabulario = v.id_vocabulario");
		query.append(" AND ");
		query.append("v.termino = ?");
		Connection conn = getConnection();
		statement = conn.prepareStatement(query.toString());
		statement.setString(1, vocabulary.getWord());
		rs = statement.executeQuery();
		while (rs.next()) {
			post = new Post();
			post.setId(rs.getInt(1));
			post.setWordFrequency(rs.getInt(2));
			if (fetch) {
				doc = post.getDocument();
				doc.setId(rs.getInt(3));
				doc.setName(rs.getString(4));
				doc.setPath(rs.getString(5));
				doc.setNumberOfWords(rs.getInt(6));
				doc.setTotalOfDocuments(rs.getInt(7));
				doc.setFileSize(rs.getLong(8));
			}
			collection.add(post);
		}
		
		closeConnection(conn);
		return collection;
	}

	@Override
	/*
	 * Selects the posts from the database, which match with the attributes of the post
	 * given
	 * @param value, the post with the attributes with which must match the collection of
	 * posts returned
	 * @return collection, a collection of posts
	 */
	protected List<Post> onSelect(Post value) throws SQLException {
		List<Post> collection = new ArrayList<Post>();
		Post p = new Post();
		query = new StringBuilder("");
		query.append("select id_posteo, id_documento, frecuencia_termino from ");
		query.append(table);
		Connection conn = getConnection();
		prepareUpdateQuery(conn, value, query);
		rs = statement.executeQuery();
		while (rs.next()) {
			p.setId(rs.getInt(1));
			p.getDocument().setId(rs.getInt(2));
			p.setWordFrequency(rs.getInt(3));
			collection.add(p);
		}
		
		
		closeConnection(conn);
		return collection;
	}

	public List<Post> onSelect(Vocabulary value) throws SQLException {
		List<Post> collection = new ArrayList<Post>();
		Post p = null;
		query = new StringBuilder("");
		query.append("select id_posteo, id_documento, frecuencia_termino from ");
		query.append(table);
		query.append(" where id_vocabulario=?");
		Connection conn = getConnection();
		this.statement = conn.prepareStatement(query.toString());
		statement.setInt(1, value.getId());
		rs = statement.executeQuery();
		while (rs.next()) {
			p = new Post();
			p.setId(rs.getInt(1));
			p.getDocument().setId(rs.getInt(2));
			p.setWordFrequency(rs.getInt(3));
			collection.add(p);
		}
		closeConnection(conn);
		return collection;
	}

	@Override
	public int count() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	/*
	 * Updates a post in the database, with the values of the post passed by parameter
	 * @param value, the post to update
	 */
	protected void onUpdate(Post value) throws SQLException {
		query = new StringBuilder("");
		query.append("update ");
		query.append(table);
		query.append(" set frecuencia_termino=?");
		query.append(" where id_posteo=?");
		Connection conn = getConnection();
		statement = conn.prepareStatement(query.toString());
		statement.setInt(1, value.getWordFrequency());
		statement.setInt(2, value.getId());
		statement.executeUpdate();
		
		closeConnection(conn);
	}

	/**
	 * prepares the query and the statement according with the non null values of the
	 * document given by parameter
	 * @param value, the document with the attributes used to build the filter
	 * @param statement, the statement to perform the query
	 * @param query, the query string
	 * @throws SQLException
	 */
	protected final void prepareUpdateQuery(Connection conn, Post value, StringBuilder query)
			throws SQLException {
		String auxQuery = "";
		ArrayList<String> dbColumns = new ArrayList<String>();
		ArrayList<Object> index = new ArrayList<Object>();
		if (value.getId() != null) {
			dbColumns.add("id_posteo");
			index.add(value.getId());
		}
		if (value.getDocument() != null) {
			dbColumns.add("id_documento");
			index.add(value.getDocument().getId());
		}
		if (value.getWordFrequency() != null) {
			dbColumns.add("frecuencia_termino");
			index.add(value.getWordFrequency());
		}
		if (dbColumns.size() > 0) {
			auxQuery = " where " + dbColumns.get(0).toString() + "=?";
			for (int i = 1; i < dbColumns.size(); i++) {
				auxQuery += " and " + dbColumns.get(i).toString() + "=?";
			}
		}
		query.append(auxQuery);
		this.statement = conn.prepareStatement(query.toString());
		prepareUpdateStatement(index);
	}
}
